using Daemon.Infrustructure.EF;
using Daemon.Repository.Contract;
using Daemon.Model;
using System;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Daemon.Data.Infrastructure.FrameWork.Query;
using Daemon.Data.Substructure.Enums;
using Daemon.Model.Entities;
namespace Daemon.Repository.EF
{
    public class LogRepository : ILogRepository
    {
        private readonly ApiDBContent _context;
        public LogRepository(ApiDBContent context)
        {
            _context = context;
        }

        public List<LogRule> GetLogRules(Type entityType, EntityChangeType entityChangeType)
        {
            return _context.LogRule.Where(e => e.EntityChangeType == entityChangeType.ToString() && e.EntityChanged == entityType.Name && e.Active).OrderBy(e => e.Priority).ToList();
        }

        public List<LogRuleReport> GetLogRuleReports(int ruleId)
        {
            return _context.LogRuleReport.Where(r => r.LogRuleId == ruleId.ToString()).ToList();
        }

        public List<LogRuleMonitoringField> GetLogRuleMonitoringFields(int logRuleId)
        {
            return _context.LogRuleMonitoringField.Where(e => e.LogRuleId == logRuleId).ToList();
        }

        public List<LogRuleCalculatedField> GetLogRuleCalculatedFields(int ruleId)
        {
            return _context.LogRuleCalculatedField.Where(r => r.LogRuleId == ruleId).ToList();
        }

        public List<LogEntityHardcodedValue> GetLogEntityHardcodedValues(string entityType)
        {
            return _context.LogEntityHardcodedValue.Where(r => r.Entity.ToLower() == entityType.ToLower()).ToList();
        }

        public void SaveLogEntry(LogEntry logEntry)
        {
            _context.LogEntry.Add(logEntry);
            _context.SaveChanges();
        }

        public void SaveLogEntityEvents(IEnumerable<LogEntityEvent> logEntityEvents)
        {
            _context.LogEntityEvent.AddRange(logEntityEvents);
            _context.SaveChanges();
        }

        public void SaveLogEntityEvent(LogEntityEvent logEntityEvent)
        {
            _context.LogEntityEvent.Add(logEntityEvent);
            _context.SaveChanges();
        }

        public void SaveLogEntityEventTrackedChanges(IEnumerable<LogEntityEventTrackedChange> logEntityEventTrackedChanges)
        {
            _context.LogEntityEventTrackedChange.AddRange(logEntityEventTrackedChanges);
            _context.SaveChanges();
        }

        public LogEntityMeta GetEntityMetaByEntityName(string entityName)
        {
            return _context.LogEntityMeta.Where(e => e.Entity.ToLower() == entityName.ToLower()).FirstOrDefault();
        }

        public List<LogEntityFKMeta> GetLogEntityFKMetas(string entityName)
        {
            return _context.LogEntityFKMeta.Where(e => e.Entity.ToLower() == entityName.ToLower()).ToList();
        }

        public string GetValueByEntityMetaReferencedInfo(string entityValue, string descriptiveNameField, string pkField, string pkValue)
        {
            string sql = $@"select {descriptiveNameField} as DescriptiveNameField from {entityValue} where {pkField} = {pkValue}";
            var referencedData = _context.Database.SqlQuery<EntityMetaInfo>(sql).ToList();
            return referencedData.FirstOrDefault().DescriptiveNameField;
        }

        public ReportingEntityDto ExecuteReportingSql(string sql)
        {
            return _context.Database.SqlQuery<ReportingEntityDto>(sql).FirstOrDefault();
        }

        public string ExecuteCalculationSql(string sql)
        {
            return _context.Database.FromSqlQuery(sql).FirstOrDefault();
        }

        public string FormatLogEntry<T>(T entity, string logMacro, IEnumerable<TrackedField> trackedFields, IEnumerable<LogRuleCalculatedField> logRuleCalculatedFields, IEnumerable<ConstraintTableInfo> constraintTableInfos, Dictionary<string, string> keyWords)
        {
            return ReplaceTerms(logMacro, entity, trackedFields, logRuleCalculatedFields, constraintTableInfos, keyWords);
        }

        public string ReplaceTerms<T>(string text, T entity, IEnumerable<TrackedField> trackedFields, IEnumerable<LogRuleCalculatedField> logRuleCalculatedFields, IEnumerable<ConstraintTableInfo> constraintTableInfos, Dictionary<string, string> keyWords)
        {
            var replacedText = text;
            var terms = ParsePlaceholderTerms(text);
            var dataToReplace = new Dictionary<string, string>();
            var isSql = text.Trim().StartsWith("select", StringComparison.OrdinalIgnoreCase);

            foreach (var term in terms)
            {
                var kv = GetKeyWordForReplace(term, keyWords);
                AddKeyValueToDictionary(kv, ref dataToReplace);
                kv = GetChangedEntityFieldForReplace(term, trackedFields, isSql);
                AddKeyValueToDictionary(kv, ref dataToReplace);
                kv = GetCalculatedFieldForReplace(term, logRuleCalculatedFields);
                AddKeyValueToDictionary(kv, ref dataToReplace);
                kv = GetLookupTableValueForReplace(term, entity, constraintTableInfos);
                AddKeyValueToDictionary(kv, ref dataToReplace);
            }

            foreach (var kv in dataToReplace)
            {
                replacedText = replacedText.Replace(kv.Key, kv.Value);
            }

            return replacedText;
        }

        public List<ConstraintTableInfo> GetReferencedTableInfos(string table)
        {
            var constraintTableInfos = GetReferencedTablesByForeignKey(table);
            foreach (var configuredConstraintTableInfo in GetConfiguredConstraintTableInfos(table))
            {
                if (!constraintTableInfos.Exists(t => t.ReferencedTableName.IgnoreCaseEquals(configuredConstraintTableInfo.ReferencedTableName) && t.ReferencedColumnName.IgnoreCaseEquals(configuredConstraintTableInfo.ReferencedColumnName)))
                {
                    constraintTableInfos.Add(configuredConstraintTableInfo);
                }
            }

            return constraintTableInfos;
        }

        public string TextValue(object value)
        {
            var strValue = string.Empty;
            if (value == null)
            {
                strValue = "null";
            }
            else
            {
                strValue = $"{value}";
            }

            return strValue;
        }

        private List<ConstraintTableInfo> GetReferencedTablesByForeignKey(string monitoringFieldTable)
        {
            string sql = $@"SELECT TABLE_NAME as `ReferencingTableName`, COLUMN_NAME as `ReferencingColumnName`, CONSTRAINT_NAME as `ConstraintName`,
REFERENCED_TABLE_NAME as `ReferencedTableName`,REFERENCED_COLUMN_NAME as `ReferencedColumnName` FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '{monitoringFieldTable}' and CONSTRAINT_NAME<>'PRIMARY';
			";

            return _context.Database.SqlQuery<ConstraintTableInfo>(sql).ToList();
        }

        private List<ConstraintTableInfo> GetConfiguredConstraintTableInfos(string entityName)
        {
            return GetLogEntityFKMetas(entityName).Select(fKMeta => new ConstraintTableInfo
            {
                ConstraintName = null,
                ReferencingTableName = entityName,
                ReferencingColumnName = fKMeta.MissingFKName,
                ReferencedTableName = fKMeta.LookupTableName,
                ReferencedColumnName = fKMeta.LookupTablePKName,
            }).ToList();
        }

        private string SqlValue(object value, Type valueType)
        {
            var strValue = string.Empty;
            if (value == null)
            {
                strValue = "null";
            }
            else if (valueType == typeof(string))
            {
                strValue = string.IsNullOrEmpty(value as string) ? "''" : $"'{value}'";
            }
            else if (valueType == typeof(int) || valueType == typeof(double))
            {
                strValue = value.ToString();
            }
            else
            {
                strValue = $"{value}";
            }

            return strValue;
        }

        private List<PlaceholderTerm> ParsePlaceholderTerms(string text)
        {
            var termCharsPattern = @"[\w\.]+";
            var termPatternDic = new Dictionary<PlaceholderTermType, string>()
            {
                { PlaceholderTermType.KeyWord, $@"(%{termCharsPattern}%)" },
                { PlaceholderTermType.ChangedEntityField, $@"(\[{termCharsPattern}\])" },
                { PlaceholderTermType.CalculatedField, $@"(~{termCharsPattern}~)" },
                { PlaceholderTermType.EntityExtraKeyValue, $@"(@{termCharsPattern}@)" },
                { PlaceholderTermType.LookupValue, $@"({{{termCharsPattern}}})" },
            };

            var terms = new List<PlaceholderTerm>();
            if (!string.IsNullOrEmpty(text))
            {
                var words = text.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                var index = -1;
                foreach (var word in words)
                {
                    foreach (var termPattern in termPatternDic)
                    {
                        var matches = new Regex(termPattern.Value).Match(word);
                        if (matches.Length > 1)
                        {
                            index++;
                            var matchValue = matches.Groups[1].Value;
                            terms.Add(new PlaceholderTerm
                            {
                                TermType = termPattern.Key,
                                RawWord = matchValue,
                                Sequence = index,
                                Word = matchValue.Substring(1, matchValue.Length - 2),
                            });

                            break;
                        }
                    }
                }
            }

            return terms;
        }

        private KeyValuePair<string, string>? GetKeyWordForReplace(PlaceholderTerm term, Dictionary<string, string> keyWords)
        {
            if (term.TermType == PlaceholderTermType.KeyWord)
            {
                var keyWordValue = keyWords.FirstOrDefault(w => w.Key.IgnoreCaseEquals(term.Word)).Value;
                if (keyWordValue != null)
                {
                    return new KeyValuePair<string, string>(term.RawWord, keyWordValue);
                }
            }

            return null;
        }

        private KeyValuePair<string, string>? GetChangedEntityFieldForReplace(PlaceholderTerm term, IEnumerable<TrackedField> trackedFields, bool isSql)
        {
            if (term.TermType == PlaceholderTermType.ChangedEntityField)
            {
                var entityField = term.Word.Split('.');
                if (entityField.Length == 2)
                {
                    var entityName = entityField[0];
                    var fieldName = entityField[1];
                    var field = trackedFields.FirstOrDefault(c => c.FieldName.IgnoreCaseEquals(fieldName));
                    var fieldValue = entityName.IgnoreCaseEquals("NEW") ? field?.NewVal :
                                     entityName.IgnoreCaseEquals("OLD") ? field?.OldVal : string.Empty;
                    return new KeyValuePair<string, string>(term.RawWord, isSql ? SqlValue(fieldValue, field.ValueType) : TextValue(fieldValue));
                }
            }

            return null;
        }

        private KeyValuePair<string, string>? GetCalculatedFieldForReplace(PlaceholderTerm term, IEnumerable<LogRuleCalculatedField> logRuleCalculatedFields)
        {
            if (term.TermType == PlaceholderTermType.CalculatedField)
            {
                var calulatedValue = logRuleCalculatedFields.FirstOrDefault(c => c.CalculatedFieldName.IgnoreCaseEquals(term.Word))?.Value;
                return new KeyValuePair<string, string>(term.RawWord, calulatedValue);
            }

            return null;
        }

        private KeyValuePair<string, string>? GetLookupTableValueForReplace<T>(PlaceholderTerm term, T entity, IEnumerable<ConstraintTableInfo> constraintTableInfos)
        {
            if (term.TermType == PlaceholderTermType.LookupValue)
            {
                var tableAndField = term.Word.Split('.');
                if (tableAndField.Length == 2)
                {
                    var tableName = tableAndField[0];
                    var fieldName = tableAndField[1];
                    var constraintTableInfo = constraintTableInfos.FirstOrDefault(t => t.ReferencedTableName.IgnoreCaseEquals(tableName));
                    if (constraintTableInfo != null)
                    {
                        var referencedPKField = constraintTableInfo.ReferencedColumnName;
                        var referencingFieldPropertyInfo = typeof(T).GetProperty(constraintTableInfo.ReferencingColumnName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                        var referencedPKValue = referencingFieldPropertyInfo?.GetValue(entity);
                        if (referencedPKValue != null)
                        {
                            var lookupFieldValue = GetLookupFieldValue(tableName, fieldName, referencedPKField, SqlValue(referencedPKValue, typeof(int)));
                            return new KeyValuePair<string, string>(term.RawWord, lookupFieldValue);
                        }
                    }
                }
            }

            return null;
        }

        private void AddKeyValueToDictionary(KeyValuePair<string, string>? keyValue, ref Dictionary<string, string> dictionary)
        {
            if (keyValue.HasValue && !dictionary.ContainsKey(keyValue.Value.Key))
            {
                dictionary.Add(keyValue.Value.Key, keyValue.Value.Value);
            }
        }

        private string GetLookupFieldValue(string table, string fieldToSelect, string pkField, string pkValue)
        {
            var sql = $"select {fieldToSelect} from {table} where {pkField}={pkValue}";
            return _context.Database.FromSqlQuery(sql).FirstOrDefault();
        }
    }

    public static class StringExetnsions
    {
        public static bool IgnoreCaseEquals(this string str1, string str2)
        {
            return string.Equals(str1, str2, StringComparison.OrdinalIgnoreCase);
        }
    }
}
